with today_cus_volume as (
select 
date_add('{{ execution_date | cst_ds }}',0) as input_time
,t2.customer_code
,max(t1.customer_name) as customer_name
,count(waybill_no) as waybill_cnt
,count(waybill_no) as acc_waybill_cnt
from 
(
    select customer_code ,customer_name,waybill_no ,date(input_time) as input_time
    from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
    where dt='{{ execution_date | cst_ds }}'
    --is_delete=1 and is_void=0  --非已删除件，非作废件 --不需要剔除
)t1 right join 
(
    select customer_code
    from jms_dwd.dwd_yl_new_customer_activity_sp_base
    where status=1 --满足条件的客户
)t2 on t1.customer_code=t2.customer_code
group by t2.customer_code
)


insert overwrite table jms_dm.dm_cus_active_laxin_volume_cnt_dt partition(dt)
select 
input_time
,customer_code
,max(customer_name) as customer_name 
,sum(waybill_cnt) as waybill_cnt
,sum(acc_waybill_cnt) as acc_waybill_cnt
,'{{ execution_date | cst_ds }}' as  date_time
,'{{ execution_date | cst_ds }}' as  dt
from 
(
    select 
    date_add('{{ execution_date | cst_ds }}',0) as  input_time
    ,customer_code
    ,customer_name
    ,0 as waybill_cnt
    ,acc_waybill_cnt 
    from jms_dm.dm_cus_active_laxin_volume_cnt_dt
    where dt=date_add('{{ execution_date | cst_ds }}',-1)
    union all 
    select * from today_cus_volume
)t group by input_time,customer_code
distribute by 1;